package com.apache.design.util;

import com.apache.info.entity.PubMetadata;
import com.apache.info.entity.PubSortInfo;
import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 数据库元数据解析工具类
 * 
 * @author zhouxianli
 *
 */
public final class DatabaseUtils {

	private Logger log = Logger.getLogger(getClass());

	private String schema;
	private static DatabaseUtils obj = new DatabaseUtils();
	private Map<String,BasicDataSource> dsMap = new HashMap<>();
	private Map<String,String> intMark = new CaseInsensitiveMap();
	private Map<String,String> longMark = new CaseInsensitiveMap();
	private Map<String,String> doubleMark = new CaseInsensitiveMap();
	private Map<String,String> dateMark = new CaseInsensitiveMap();

	/**
	 * 禁止实例化
	 */
	private DatabaseUtils() {
		intMark.put("TINYINT","0");
		intMark.put("SMALLINT","0");
		intMark.put("MEDIUMINT","0");
		intMark.put("INT","0");
		intMark.put("INTEGER","0");
		longMark.put("BIGINT","0");
		longMark.put("LONG","0");
		doubleMark.put("NUMBER","0");
		doubleMark.put("DOUBLE","0");
		doubleMark.put("DECIMAL","0");
		dateMark.put("DATE","0");
		dateMark.put("TIME","0");
		dateMark.put("YEAR","0");
		dateMark.put("DATETIME","0");
		dateMark.put("TIMESTAMP","0");
	}

	public static DatabaseUtils getInstance() {
		return obj;
	}

	public Connection getConnection(String sysEname,String url, String DriverClassName, String user, String password) {
		try {
			if(!dsMap.containsKey(sysEname)) {
				BasicDataSource ds = new BasicDataSource();
				ds.setDriverClassName(DriverClassName);
				ds.setUrl(url);
				ds.setUsername(user);
				ds.setPassword(password);
				ds.setMaxTotal(2);
				ds.setMaxIdle(1);
				dsMap.put(sysEname,ds);
			}
			return dsMap.get(sysEname).getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			log.warn("{}", e);
			dsMap.remove(sysEname);
		}
		return null;
	}

	public void removeDataSource(String sysEname){
		if(dsMap.containsKey(sysEname)) {
			try {
				dsMap.get(sysEname).close();
			} catch (SQLException e) {
			}
			dsMap.remove(sysEname);
		}
	}

	public List<PubSortInfo> getAllTableNamesMap(Connection conn,String sysEname) {
		ResultSet rs = null;
		List<PubSortInfo> result = new ArrayList<>();
		try {
			DatabaseMetaData metaData = conn.getMetaData();
			rs = metaData.getTables(null, schema, null, new String[] { "TABLE" });
			while (rs.next()) {
				PubSortInfo info = new PubSortInfo();
				String tableName = rs.getString("TABLE_NAME");
				info.setInfoTableName(tableName);
				info.setObjName(GZipUtil.toCamelNamed(tableName));
				info.setSysName(sysEname);
				result.add(info);
			}
		} catch (SQLException e) {
			log.warn("{}", e);
		} finally {
			close(null,rs);
		}
		return result;
	}

	public List<PubMetadata> getTableColumns(Connection conn,String tableName) {
		List<PubMetadata> columns = new ArrayList<>();

		ResultSet rs = null;
		try {
			List<String> prmKeys = getTablePrimaryKeys(conn,tableName);
			// 获取字段基本信息
			DatabaseMetaData metaData = conn.getMetaData();
			rs = metaData.getColumns(null, schema, tableName, "%");
			while (rs.next()) {
				PubMetadata column = new PubMetadata();
				String columnName = rs.getString("COLUMN_NAME");
				column.setDataShortName(columnName);
				column.setDataAttr(GZipUtil.toCamelNamed(columnName));
				String type = rs.getString("TYPE_NAME");
				if(intMark.containsKey(type)){
					column.setDataType("1");
				}else if(doubleMark.containsKey(type)){
					column.setDataType("3");
				}else if(longMark.containsKey(type)){
					column.setDataType("2");
				}else if(dateMark.containsKey(type)){
					column.setDataType("6");
				}else{
					column.setDataType("5");
				}
				String columnSize = rs.getString("COLUMN_SIZE");
				column.setDataLength(Long.valueOf(columnSize));
				if("1".equalsIgnoreCase(rs.getString("NULLABLE"))){
					column.setDataRestrict("3");
				}else{
					if(prmKeys.contains(columnName) && prmKeys.size()>1){
						column.setDataRestrict("4");
					} else if(prmKeys.contains(columnName)){
						column.setDataRestrict("1");
					}else{
						column.setDataRestrict("2");
					}
				}
				columns.add(column);
			}
		} catch (SQLException e) {
			log.warn("{}", e);
		} finally {
			close(null,rs);
		}

		return columns;
	}

	public void close(Connection con,ResultSet rs) {
		try {
			if (null != rs)
				rs.close();
		} catch (SQLException e) {
			log.error("关闭连接出错；异常信息：" + e.getMessage());
		} finally {
			try {
				if (null != con)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void main(String[] args) {
		Connection con = DatabaseUtils.getInstance().getConnection("test","jdbc:mysql://10.0.6.13:3306/healthchannel?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&useSSL=false","com.mysql.jdbc.Driver","health","abcd@123");
		DatabaseUtils.getInstance().getTableColumns(con,"request_permission");
		DatabaseUtils.getInstance().close(con,null);
	}

	private List<String> getTablePrimaryKeys(Connection conn,String tableName) {
		List<String> prmKeys = new ArrayList<String>();
		ResultSet rs = null;
		try {
			// 获取字段基本信息
			DatabaseMetaData metaData = conn.getMetaData();
			rs = metaData.getPrimaryKeys(null, schema, tableName);
			while (rs.next()) {
				prmKeys.add(rs.getString("COLUMN_NAME"));
			}
			rs.close();

		} catch (SQLException e) {
			log.warn("{}", e);
		}
		return prmKeys;
	}
}
